#include "DatabaseManager.h"
#include <QDebug>
#include <QSqlRecord>

DatabaseManager& DatabaseManager::instance() {
    static DatabaseManager instance;
    return instance;
}

DatabaseManager::DatabaseManager(QObject* parent) : QObject(parent) {
    m_connectionName = QString("DatabaseManager_%1").arg(quintptr(this));
}

DatabaseManager::~DatabaseManager() {
    disconnect();
}

bool DatabaseManager::connect(const QString& host, int port, const QString& database,
                              const QString& username, const QString& password,
                              const QString& connectionName) {
    QMutexLocker locker(&m_mutex);

    if (m_database.isOpen()) {
        m_database.close();
    }

    QString actualConnectionName = connectionName.isEmpty() ? m_connectionName : connectionName;
    m_database = QSqlDatabase::addDatabase("QMYSQL", actualConnectionName);
    m_database.setHostName(host);
    m_database.setPort(port);
    m_database.setDatabaseName(database);
    m_database.setUserName(username);
    m_database.setPassword(password);

    // 设置连接选项
    m_database.setConnectOptions("MYSQL_OPT_RECONNECT=1");

    if (!m_database.open()) {
        m_lastError = m_database.lastError().text();
        qWarning() << "Database connection failed:" << m_lastError;
        return false;
    }

    qDebug() << "Database connected successfully to" << database;
    return true;
}

void DatabaseManager::disconnect() {
    QMutexLocker locker(&m_mutex);
    if (m_database.isOpen()) {
        m_database.close();
    }
    if (!m_connectionName.isEmpty()) {
        QSqlDatabase::removeDatabase(m_connectionName);
    }
}

bool DatabaseManager::isConnected() const {
    QMutexLocker locker(&m_mutex);
    return m_database.isOpen();
}

QString DatabaseManager::lastError() const {
    QMutexLocker locker(&m_mutex);
    return m_lastError;
}

bool DatabaseManager::insertData(const QString& tableName, const QVariantMap& data,
                                 const QStringList& jsonFields) {
    QMutexLocker locker(&m_mutex);

    if (!isConnected() || data.isEmpty()) {
        m_lastError = "Not connected or empty data";
        return false;
    }

    QStringList fieldNames;
    QStringList placeholders;
    QSqlQuery query(m_database);

    // 构建字段名和占位符
    for (auto it = data.begin(); it != data.end(); ++it) {
        fieldNames.append(it.key());
        placeholders.append(":" + it.key());
    }

    QString sql = QString("INSERT INTO %1 (%2) VALUES (%3)")
                      .arg(tableName)
                      .arg(fieldNames.join(", "))
                      .arg(placeholders.join(", "));

    query.prepare(sql);

    // 绑定参数
    for (auto it = data.begin(); it != data.end(); ++it) {
        QVariant processedValue = processValueForJson(it.value(), jsonFields.contains(it.key()));
        query.bindValue(":" + it.key(), processedValue);
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qWarning() << "Insert data failed:" << m_lastError;
        return false;
    }

    return true;
}

bool DatabaseManager::updateData(const QString& tableName, const QVariantMap& data,
                                 const QString& whereClause, const QVariantMap& whereParams,
                                 const QStringList& jsonFields) {
    QMutexLocker locker(&m_mutex);

    if (!isConnected() || data.isEmpty()) {
        m_lastError = "Not connected or empty data";
        return false;
    }

    QStringList setClauses;
    QSqlQuery query(m_database);

    // 构建SET子句
    for (auto it = data.begin(); it != data.end(); ++it) {
        setClauses.append(it.key() + " = :set_" + it.key());
    }

    QString sql = QString("UPDATE %1 SET %2").arg(tableName).arg(setClauses.join(", "));

    // 添加WHERE子句
    if (!whereClause.isEmpty()) {
        sql += " WHERE " + whereClause;  // 直接使用原始 WHERE 子句
    }

    query.prepare(sql);

    // 绑定SET参数
    for (auto it = data.begin(); it != data.end(); ++it) {
        QString paramName = ":set_" + it.key();
        QVariant processedValue = processValueForJson(it.value(), jsonFields.contains(it.key()));
        query.bindValue(paramName, processedValue);
    }

    // 绑定WHERE参数
    for (auto it = whereParams.begin(); it != whereParams.end(); ++it) {
        query.bindValue(":" + it.key(), it.value());
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qWarning() << "Update data failed:" << m_lastError << "SQL:" << sql;
        return false;
    }

    return true;
}

bool DatabaseManager::deleteData(const QString& tableName, const QString& whereClause,
                                 const QVariantMap& whereParams) {
    QMutexLocker locker(&m_mutex);

    if (!isConnected()) {
        m_lastError = "Not connected";
        return false;
    }

    QSqlQuery query(m_database);
    QString sql = QString("DELETE FROM %1").arg(tableName);

    if (!whereClause.isEmpty()) {
        sql += " WHERE " + whereClause;  // 直接使用原始 WHERE 子句
    }

    query.prepare(sql);

    // 绑定WHERE参数
    for (auto it = whereParams.begin(); it != whereParams.end(); ++it) {
        query.bindValue(":" + it.key(), it.value());
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qWarning() << "Delete data failed:" << m_lastError << "SQL:" << sql;
        return false;
    }

    return true;
}

QVariantMap DatabaseManager::selectCategory(const QString &category)
{
    QMutexLocker locker(&m_mutex);

    if (!isConnected()) {
        m_lastError = "Not connected";
        return QVariantMap();
    }
    QVariantMap results;
    QSqlQuery sql_query(m_database);
    QString sql = QString("SELECT payload FROM param_settings WHERE category = '%1'").arg(category);
    // 执行查询
    if (!sql_query.exec(sql)) {
        m_lastError = sql_query.lastError().text();
        return results;
    }
    if (sql_query.exec()) {
        while (sql_query.next()) {
            results = rowToMap(sql_query);
        }
    }

    return results;
}

QVariantMap DatabaseManager::selectOne(const QString& tableName, const QStringList& columns,
                                       const QString& whereClause, const QVariantMap& whereParams,
                                       const QStringList& jsonFieldsToParse) {
    QMutexLocker locker(&m_mutex);

    if (!isConnected()) {
        m_lastError = "Not connected";
        return QVariantMap();
    }
    QVariantMap results;
    QSqlQuery sql_query(m_database);
    QString columnStr = columns.contains("*") ? "*" : columns.join(", ");
    // QString sql = QString("SELECT %1 FROM %2").arg(columnStr).arg(tableName);
    QString sql = "SELECT payload FROM param_settings WHERE category = 'algorithm'";
    // 执行查询
    if (!sql_query.exec(sql)) {
        m_lastError = sql_query.lastError().text();
        return results;
    }
    if (sql_query.exec()) {
        while (sql_query.next()) {
            results = rowToMap(sql_query, jsonFieldsToParse);
        }
    }

    return results;
    // if (!whereClause.isEmpty()) {
    //     sql += " WHERE " + whereClause;  // 直接使用原始 WHERE 子句
    // }

    // sql += " LIMIT 1";

    // query.prepare(sql);

    // // 绑定WHERE参数
    // for (auto it = whereParams.begin(); it != whereParams.end(); ++it) {
    //     query.bindValue(":" + it.key(), it.value());
    // }

    // if (query.exec() && query.next()) {
    //     qWarning() << "query:" << jsonFieldsToParse;
    //     return rowToMap(query, jsonFieldsToParse);
    // } else {
    //     if (query.lastError().isValid()) {
    //         m_lastError = query.lastError().text();
    //         qWarning() << "Select one failed:" << m_lastError << "SQL:" << sql;
    //     }
    // }

    // return QVariantMap();
}

QList<QVariantMap> DatabaseManager::selectAll(const QString& tableName, const QStringList& columns,
    const QString& whereClause, const QVariantMap& whereParams,
    const QStringList& jsonFieldsToParse) {
    QMutexLocker locker(&m_mutex);
    QList<QVariantMap> results;

    //if (!isConnected()) {
    //    m_lastError = "Not connected";
    //    qWarning() << "Database not connected";
    //    return results;
    //}

    //// 构建基础 SQL
    //QString columnStr = columns.contains("*") ? "*" : columns.join(", ");
    //QString sql = QString("SELECT %1 FROM %2").arg(columnStr).arg(tableName);

    //// 处理 WHERE 子句
    //QString finalWhereClause = whereClause;
    //QVariantMap allBindParams;

    //// 如果有 WHERE 条件，添加到 SQL
    //if (!whereClause.isEmpty()) {
    //    sql += " WHERE " + whereClause;
    //    allBindParams = whereParams; // 直接使用传入的参数
    //}

    //QString sql2 = "SELECT * FROM hardware_task_queue WHERE status = :status";
    //QSqlQuery query(m_database);
    //query.prepare(sql2);

    //query.bindValue(":status", 0);
    //// 绑定所有参数 - 使用与 replaceData 相同的简单方式
    //for (auto it = allBindParams.begin(); it != allBindParams.end(); ++it) {
    //    QString paramName = ":" + it.key();
    //    int xx = it.value().toInt();
    //    query.bindValue(paramName, xx);
    //    qDebug() << "Binding:" << paramName << "=" << xx;
    //}

    QSqlQuery sql_query(m_database);

    //sql_query.prepare("SELECT * FROM hardware_task_queue WHERE status = :status");

    //sql_query.bindValue(":status", 0);
    QString sql = "SELECT * FROM hardware_task_queue WHERE status = 0";

    // 执行查询
    if (!sql_query.exec(sql)) {
        m_lastError = sql_query.lastError().text();
        return results;
    }
    if (sql_query.exec()) {
        while (sql_query.next()) {
            results.append(rowToMap(sql_query, jsonFieldsToParse));
        }
    }

    return results;
}

bool DatabaseManager::updateJsonField(const QString& tableName, const QString& jsonFieldName,
                                      const QJsonObject& jsonData, const QString& whereClause,
                                      const QVariantMap& whereParams) {
    QVariantMap data;
    data[jsonFieldName] = QVariant::fromValue(jsonData);

    return updateData(tableName, data, whereClause, whereParams, {jsonFieldName});
}

QJsonObject DatabaseManager::getJsonField(const QString& tableName, const QString& jsonFieldName,
                                          const QString& whereClause, const QVariantMap& whereParams) {
    QVariantMap result = selectOne(tableName, {jsonFieldName}, whereClause, whereParams, {jsonFieldName});

    if (result.contains(jsonFieldName)) {
        QVariant value = result[jsonFieldName];
        if (value.canConvert<QJsonObject>()) {
            return value.toJsonObject();
        }
    }

    return QJsonObject();
}

bool DatabaseManager::executeTransaction(const std::function<bool()>& transactionOperations) {
    QMutexLocker locker(&m_mutex);

    if (!isConnected()) {
        m_lastError = "Not connected";
        return false;
    }

    if (!m_database.transaction()) {
        m_lastError = "Begin transaction failed";
        return false;
    }

    try {
        bool success = transactionOperations();
        if (success) {
            if (!m_database.commit()) {
                m_lastError = "Commit transaction failed";
                m_database.rollback();
                return false;
            }
            return true;
        } else {
            m_database.rollback();
            return false;
        }
    } catch (const std::exception& e) {
        m_database.rollback();
        m_lastError = QString("Transaction exception: %1").arg(e.what());
        return false;
    }
}

bool DatabaseManager::batchInsert(const QString& tableName, const QList<QVariantMap>& dataList,
                                  const QStringList& jsonFields) {
    return executeTransaction([&]() {
        for (const auto& data : dataList) {
            if (!insertData(tableName, data, jsonFields)) {
                return false;
            }
        }
        return true;
    });
}

bool DatabaseManager::upsertData(const QString& tableName, const QVariantMap& data,
                                 const QString& whereClause, const QVariantMap& whereParams,
                                 const QStringList& jsonFields) {
    QMutexLocker locker(&m_mutex);

    if (!isConnected() || data.isEmpty()) {
        m_lastError = "Not connected or empty data";
        return false;
    }

    // 先尝试更新（不包含ID字段）
    QVariantMap vData = data;
    vData.remove("id"); // 移除ID字段，避免更新时冲突

    if (updateData(tableName, vData, whereClause, whereParams, jsonFields)) {
        QSqlQuery query(m_database);
        query.prepare(QString("SELECT ROW_COUNT()"));
        if (query.exec() && query.next()) {
            int affectedRows = query.value(0).toInt();
            if (affectedRows > 0) {
                return true; // 更新成功
            }
        }
    }

    // 更新失败（记录不存在），尝试插入（包含ID字段）
    return insertData(tableName, data, jsonFields);
}

bool DatabaseManager::replaceData(const QString &tableName, const QVariantMap &data, const QStringList &jsonFields)
{
    QMutexLocker locker(&m_mutex);

    if (!isConnected() || data.isEmpty()) {
        m_lastError = "Not connected or empty data";
        return false;
    }

    QStringList fieldNames;
    QStringList placeholders;
    QSqlQuery query(m_database);

    // 构建字段名和占位符
    for (auto it = data.begin(); it != data.end(); ++it) {
        fieldNames.append(it.key());
        placeholders.append(":" + it.key());
    }

    // 使用 REPLACE INTO 而不是 INSERT INTO
    QString sql = QString("REPLACE INTO %1 (%2) VALUES (%3)")
                      .arg(tableName)
                      .arg(fieldNames.join(", "))
                      .arg(placeholders.join(", "));

    query.prepare(sql);

    // 绑定参数
    for (auto it = data.begin(); it != data.end(); ++it) {
        QVariant processedValue = processValueForJson(it.value(), jsonFields.contains(it.key()));
        query.bindValue(":" + it.key(), processedValue);
    }

    if (!query.exec()) {
        m_lastError = query.lastError().text();
        qWarning() << "Replace data failed:" << m_lastError;
        return false;
    }

    return true;
}

QVariantMap DatabaseManager::rowToMap(const QSqlQuery& query, const QStringList& jsonFieldsToParse) {
    QVariantMap map;
    QSqlRecord record = query.record();

    for (int i = 0; i < record.count(); ++i) {
        QString fieldName = record.fieldName(i);
        QVariant value = record.value(i);

        // 只在明确指定的字段上进行JSON解析
        bool isJsonField = jsonFieldsToParse.contains(fieldName);
        map[fieldName] = processValueForJson(value, isJsonField);
    }

    return map;
}

QVariant DatabaseManager::processValueForJson(const QVariant& value, bool isJsonField) {
    if (!isJsonField) {
        return value; // 非JSON字段直接返回
    }

    // JSON字段处理：写入时序列化，读取时解析
    if (value.type() == QVariant::String) {
        // 读取时的JSON字符串解析
        QString strValue = value.toString().trimmed();
        if (strValue.startsWith('{') && strValue.endsWith('}')) {
            QJsonDocument doc = QJsonDocument::fromJson(strValue.toUtf8());
            if (doc.isObject()) {
                return doc.object();
            }
        }
    } else if (value.canConvert<QJsonObject>()) {
        // 写入时的JSON对象序列化
        QJsonObject jsonObj = value.toJsonObject();
        QJsonDocument doc(jsonObj);
        return doc.toJson(QJsonDocument::Compact);
    }

    return value;
}
